package sql;

import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import sql.record.Registration;
import sql.record.SQLType;
import sql.record.TableRecord;

import logging.Monitor;

/**
 * This service class offers all the common operation we intend to perform 
 * on a database:
 * <ul>
 * 	<li>Creating and destroying a database</li>
 * 	<li>Creating and destroying a database table</li>
 * 	<li>Inserting a record to a database</li>
 * 	<li>Querying for a record</li>
 * 	<li>Removing a record from a database<li>
 * </ul>
 * But it's important to notice that it only creates the commands without performing 
 * them, the class responsible for it is: {@link DBManagment}.
 * @author Ron Galay
 *
 */
public class QueryFactory {

	// TODO make both of this functions general - for instance take as a parameter the db name
	/**
	 * Creates a create db query 
	 * @return the query that {@link DBManagment} receives
	 */
	public static String generateCreateDBQuery() {
		
		return "CREATE DATABASE IF NOT EXISTS srgalayDB";
	}
	
	/**
	 * @return the query that {@link DBManagment} receives
	 */
	public static String generaeteRemoveDBQuery() {
		
		return "DROP DATABASE IF EXISTS srglayDB";
	}
	
	/**
	 * Creating keys for the table by getting all the fields marked by the
	 * primKey tag, as can be seen in {@link SQLType}
	 * @param record to insert to the db
	 * @return the query that {@link DBManagment} receives
	 */
	public static String generateCreateTableQuery(TableRecord record) {
		
		Map<String,String> values = record.getSchema();
		String schema = "";
		for(String column: record.getFields()) {
			schema = schema+" "+column+" "+values.get(column)+",";
		}
		String primeKey = record.getPrimeKeyName();
		schema = 	(primeKey==null)?
					"("+schema.substring(0, schema.length()-1)+")":
					"("+schema+" PRIMARY KEY ("+primeKey+") )";
		
		String createTable = 	"CREATE TABLE IF NOT EXISTS " + 
								record.getTableName() +
								schema +
								" ENGINE=InnoDB";
		Monitor.write(null,"Using this query to create a table:"+createTable);
		
		return createTable;
	}
	
	/**
	 * Creating the drop table query. The table name is received by inspecting
	 * the class name of the instance.
	 * @param record from which we extract the table name to drop
	 * @return the query that {@link DBManagment} receives
	 */
	public static String generateDropTableQuery(TableRecord record) {
		
		String dropTable = "DROP TABLE IF EXISTS "+record.getTableName();
		Monitor.write(null,"Using this query to drop the table:"+dropTable);
		
		return dropTable;
	}
	
	/**
	 * Inserts the passes record to a table. Both the table and the actual
	 * values are extracted by reflection.
	 * As for similar queries, this assumes that the database already exists.
	 * @return the query that {@link DBManagment} receives
	 */
	public static String generateInsertQuery(TableRecord record) {
		
		List<String> columns = record.getFields();
		
		String columnList = "";
		for(String column: columns) {
			columnList = columnList+column+",";
		}
		// removing the trailing ',' at the end
		columnList = "("+columnList.substring(0, columnList.length()-1)+")";
		
		String valueList = "";
		for(@SuppressWarnings("unused") String column: columns) {
			valueList = valueList+"?,";
		}
		// removing the trailing ',' at the end
		valueList = "("+valueList.substring(0, valueList.length()-1)+")";
		
		String insert = "INSERT INTO "+record.getTableName()+" "+
						columnList+" VALUES "+
						valueList;

		Monitor.write(null,"Using this query to insert a record to the table:"+insert);
		
		return insert;
	}
	
	public static String generateUpdateQuery(TableRecord record) {
		
		List<String> fields = record.getFields();
		String updateList = "";
		for(String field: fields) {
			updateList = updateList+field+"= ? , ";
		}
		// removing the trailing ','
		updateList = updateList.substring(0, updateList.length()-2);
		String searchName = record.getSearchName();
		
		String updateQuery = "UPDATE "+record.getTableName()+" SET "+
							 updateList +
							 " WHERE "+searchName+"=?";
		
		Monitor.write(null,"Using this query to insert a update to the table:"+updateQuery);
		return updateQuery;
	}
	
	/**
	 * Creates the get query.
	 * @param record which we try to search
	 * @return the query that {@link DBManagment} receives
	 */
	public static String genereateGetRecordQuery(TableRecord record) {
		
		String tableName = record.getClass().getSimpleName();
		String searchKey = record.getSearchName();
		String getRecord = "SELECT * FROM "+
							tableName+
							" WHERE "+
							searchKey+"=?";
		
		Monitor.write(null,"Using this query to get a record from the table:"+getRecord);
		return getRecord;
	}
	
	/**
	 * 
	 * @param tableName
	 * @return
	 */
	public static String generateGetTable(String tableName) {
		
		return "SELECT * FROM " + tableName;
	}
	
	/**
	 * 
	 * @return
	 */
	public static String generateJoin() {
		
		String join = 	"SELECT * FROM "+
						" Course LEFT JOIN CourseSchedule"+
						" ON Course.courseName = CourseSchedule.courseName";
		return join;
	}
	
	public static String generateGetMyCourses(String userName) {
		
		String myCourses = 	" SELECT * FROM "+
							" Course LEFT JOIN CourseSchedule AS schedule"+
							" ON Course.courseName = schedule.courseName"+
							" WHERE EXISTS ("+
									" SELECT * FROM " +
										" Registration LEFT JOIN CourseSchedule" +
										" ON Registration.courseName = CourseSchedule.courseName" +
										" WHERE Registration.userName = '"+userName+"' AND "+
												"CourseSchedule.courseName = schedule.courseName)";
		return myCourses;
	}
	
	/**
	 * 
	 * @return
	 */
	public static String generateAvailableCourses(String userName) {
		
		String available = 	" SELECT * FROM "+
							" Course LEFT JOIN CourseSchedule AS schedule"+
							" ON Course.courseName = schedule.courseName"+
							" WHERE NOT EXISTS ("+
										" SELECT * FROM " +
										" Registration LEFT JOIN CourseSchedule" +
										" ON Registration.courseName = CourseSchedule.courseName" +
										" WHERE (Registration.userName = '"+userName+"') AND "+
												"((CourseSchedule.day = schedule.day) AND"+
												"(" +
												"(CourseSchedule.startHour > schedule.startHour AND " +
												"CourseSchedule.startHour < schedule.endHour) OR " +
												"(CourseSchedule.endHour > schedule.startHour AND " +
												"CourseSchedule.endHour < schedule.endHour) OR " +
												"(CourseSchedule.startHour <= schedule.startHour AND " +
												"CourseSchedule.endHour >= schedule.endHour)" +
												"))) AND " +
							" (SELECT COUNT(*) FROM " +
							" Registration " +
							" WHERE Registration.courseName = schedule.courseName) < Course.capacity";
		return available;
	}
	
	public static String generateFindColliisons(List<TableRecord> registrations) {
		
		List<String> reg = new LinkedList<String>();
		for(TableRecord record:registrations) {
			reg.add(((Registration)record).courseName);
		}
		
		String whereClause = "";
		for(String courses:reg) {
			
			whereClause = whereClause+"schedule.courseName='"+courses+"' OR ";
		}
		// removing the trailing ' AND '
		whereClause = whereClause.substring(0, whereClause.length()-" OR ".length());
		
		String whereClause2 = "";
		for(String courses:reg) {
			
			whereClause2 = whereClause2+"CourseSchedule.courseName='"+courses+"' OR ";
		}
		// removing the trailing ' AND '
		whereClause2 = whereClause2.substring(0, whereClause2.length()-" OR ".length());
		
		String collisionFree =  "SELECT * FROM " +
								"CourseSchedule AS schedule "+
								"WHERE "+"("+whereClause+")"+" AND " +
								"NOT EXISTS( " +
											"SELECT * FROM "+
											"CourseSchedule "+
											"WHERE ((" +
												whereClause2+") AND ("+
												"CourseSchedule.courseName != schedule.courseName) AND "+
												"((CourseSchedule.day = schedule.day) AND "+
												"(" +
												"(CourseSchedule.startHour > schedule.startHour AND " +
												"CourseSchedule.startHour < schedule.endHour) OR " +
												"(CourseSchedule.endHour > schedule.startHour AND " +
												"CourseSchedule.endHour < schedule.endHour) OR " +
												"(CourseSchedule.startHour <= schedule.startHour AND " +
												"CourseSchedule.endHour >= schedule.endHour)" +
												"))))";
		
		Monitor.write(null, "Using this query to find all the collision free courses:"+collisionFree);
		return collisionFree;
	}
	
	/**
	 * Creates the remove query.
	 * @param record which we want to remove
	 * @return the query that {@link DBManagment} receives
	 */
	public static String generateRemoveRecordQuery(TableRecord record) {
		
		String tableName = record.getTableName();
		String searchName = record.getSearchName();
		String removeRecord = 	"DELETE FROM "+
								tableName+
								" WHERE "+
								searchName+"=?";
		
		Monitor.write(null, "Using this query to get remove a record from the table:"
							+removeRecord);
		return removeRecord;
		
	}
}
